package dbop;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.log4j.Logger;

public class DBCPDatabaseAccess {


	private Statement stmt = null;
	private ResultSet rs = null;
	private Connection conn = null;
	private PreparedStatement prestmt = null;

	private volatile boolean pmdKnownBroken = false;
	private static Logger logger = Logger.getLogger(DBManager.class);
	public DBCPDatabaseAccess() {

		//
		//
		// try
		// {
		// Class.forName(sDBDriver);
		// this.conn=DriverManager.getConnection(sConnStr,sUser,sPassword);
		// }
		// catch(Exception ex){
		// System.err.println("aq.executeQuery:   " + ex.getMessage());
		// log.loger.error("error", ex);
		// }
	}

	public ResultSet StatExecuteQuery(String sql) {
		try {
			this.conn = DBManager.generateDBConn();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			// prestmt.close();

		} catch (Exception ex) {
			// System.err.println("aq.executeQuery:   " + ex.getMessage());
			logger.error("StatExecuteQuery error:   " + ex);
		}
		// finally
		// {
		// try {
		// stmt.close();
		// } catch (Exception e) {
		// logger.error("PreStatExecuteQuery error:   " + e);
		// }
		// }

		return rs;

	}

	public ResultSet PreStatExecuteQuery(String sql, String paramArray[]) {
		try {
			this.conn = DBManager.generateDBConn();
			prestmt = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			// int jjj=prestmt.getParameterMetaData().getParameterCount();
			for (int i = 0; i < paramArray.length; i++) {
				prestmt.setObject(i + 1, paramArray[i]);
			}
			rs = prestmt.executeQuery();
		} catch (Exception ex) {
			// System.err.println("aq.executeQuery:   " + ex.getMessage());
			logger.error("PreStatExecuteQuery error:   " + ex);
		}
		// finally
		// {
		// try {
		// prestmt.close();
		// } catch (Exception e) {
		// logger.error("PreStatExecuteQuery error:   " + e);
		// }
		// }

		return rs;
	}

	public ResultSet PreStatExecuteQuery(String sql, Object paramArray[]) {
		try {
			this.conn = DBManager.generateDBConn();
			prestmt = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			// int jjj=prestmt.getParameterMetaData().getParameterCount();
			for (int i = 0; i < paramArray.length; i++) {
				prestmt.setObject(i + 1, paramArray[i]);
			}
			rs = prestmt.executeQuery();
		} catch (Exception ex) {
			System.err.println("aq.executeQuery:   " + ex.getMessage());
			logger.error("PreStatExecuteQuery error:   " + ex);
		}
		// finally
		// {
		// try {
		// prestmt.close();
		// } catch (Exception e) {
		// logger.error("PreStatExecuteQuery error:   " + e);
		// }
		// }

		return rs;
	}

	public int PreStatExecuteUpdate(String sql, Object paramArray[]) {
		int updateNum = 0;
		try {
			this.conn = DBManager.generateDBConn();
			if (conn==null){
				return -1;
			}
			prestmt = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			// int jjj=prestmt.getParameterMetaData().getParameterCount();
			for (int i = 0; i < paramArray.length; i++) {
				prestmt.setObject(i + 1, paramArray[i]);
			}
			updateNum = prestmt.executeUpdate();
		} catch (Exception ex) {
			logger.error(sql+": ExecuteBatch error:   " + ex);
			try {
				conn.rollback();
			} catch (Exception e) {
				logger.error(sql+": ExecuteBatch rollback error:   " + e);
			}
			return -1;
		} finally {
			try {
				conn.setAutoCommit(true);
				// if(!prestmt.isClosed())
				// {
				// prestmt.close();
				// }
			} catch (Exception e) {
				logger.error(sql+": ExecuteBatch setAutoCommit(true) error:   " + e);
			}
		}

		return updateNum;
	}

	public int[] ExecuteBatch(String sql, Object[][] params) {
		try {
			this.conn = DBManager.generateDBConn();
			conn.setAutoCommit(false);// 取消自动提交

			int[] rows = null;
			prestmt = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				for (int j = 0; j < params[i].length; j++) {
					if (params[i][j] != null) {
						prestmt.setObject(j + 1, params[i][j]);
					}
				}
				prestmt.addBatch();
			}
			rows = prestmt.executeBatch();
			conn.commit();

			return rows;
		} catch (Exception ex) {
			logger.error(sql+" ExecuteBatch error:   " + ex);
			try {
				conn.rollback();
			} catch (Exception e) {
				logger.error(sql+" ExecuteBatch rollback error:   " + e);
			}
			return null;
		} finally {
			try {
				conn.setAutoCommit(true);
				// if(!prestmt.isClosed())
				// {
				prestmt.close();
				// }
			} catch (Exception e) {
				logger.error(sql+" ExecuteBatch setAutoCommit(true) error:   " + e);
			}
		}
	}

	public int[] ExecuteBatch(String sql, ArrayList<Object[]> params) {
		
		int[] rows = null;
		
		if(params==null || params.size()==0)
		{
			return rows;
		}
		
		try {
			this.conn = DBManager.generateDBConn();
			if (conn==null){
				return null;
			}
			conn.setAutoCommit(false);// 取消自动提交
			prestmt = conn.prepareStatement(sql);
			for (Object[] param : params) {
				for (int j = 0; j < param.length; j++) {
					if (param[j] != null) {
						prestmt.setObject(j + 1, param[j]);
					} else {
						prestmt.setObject(j + 1, null);
					}

				}
				prestmt.addBatch();
			}
			rows = prestmt.executeBatch();
			conn.commit();
			return rows;
		} catch (Exception ex) {
			logger.error("ExecuteBatch error:   " + ex);
			try {
				conn.rollback();
			} catch (Exception e) {
				logger.error("ExecuteBatch rollback error:   " + e);
			}
			return null;
		} finally {
			try {
				conn.setAutoCommit(true);
				// if(!prestmt.isClosed())
				// {
				prestmt.close();
				// }
			} catch (Exception e) {
				logger.error("ExecuteBatch setAutoCommit(true) error:   " + e);
			}
		}
	}
	
	/*
	 * 调用无返回值的存储过程,且具有一套输入参数
	 */
	public int[] ExecuteNoResultsProcedure(String procedureName,Object[][] readObject)
    {
    	
    	CallableStatement proc = null;
    	int[] rows = null;
    	
    	try {
    		this.conn = DBManager.generateDBConn();
    		conn.setAutoCommit(false);//取消自动提交
    		proc = conn.prepareCall("{ call "+ procedureName +" }");
    		for (Object[] param:readObject)
            {
                for(int j=0;j<param.length;j++)
                {
                    if (param[j] != null)
                    {
                    	proc.setObject(j + 1, param[j]);
                    }
                    else
                    {
                    	proc.setObject(j + 1,null);
                    }
                }
                proc.addBatch();
            }
    		rows=proc.executeBatch();
    		conn.commit();
        	return rows;
    	} catch (Exception ex) {
			logger.error(procedureName+" ExecuteBatch error:   " + ex);
			try {
				conn.rollback();
			} catch (Exception e) {
				logger.error(procedureName+" ExecuteBatch rollback error:   " + e);
			}
			return null;
		} finally {
			try {
				conn.setAutoCommit(true);
				// if(!prestmt.isClosed())
				// {
				proc.close();
				// }
			} catch (Exception e) {
				logger.error( procedureName+ " ExecuteBatch setAutoCommit(true) error:   " + e);
			}
		}
	}
	public int[] ExecuteNoResultsProcedure(String procedureName,List<Object[]> readObject)
    {
    	CallableStatement proc = null;
    	int[] rows = null;
    	
    	try {
    		this.conn = DBManager.generateDBConn();
    		conn.setAutoCommit(false);//取消自动提交
    		proc = conn.prepareCall("{ call "+ procedureName +" }");
    		for (Object[] param:readObject)
            {
                for(int j=0;j<param.length;j++)
                {
                    if (param[j] != null)
                    {
                    	proc.setObject(j + 1, param[j]);
                    }
                    else
                    {
                    	proc.setObject(j + 1,null);
                    }
                }
                proc.addBatch();
            }
    		rows=proc.executeBatch();
    		conn.commit();
        	return rows;
    	} catch (Exception ex) {
    		ex.printStackTrace();
			logger.error(procedureName+" ExecuteBatch error:   " + ex);
			try {
				conn.rollback();
			} catch (Exception e) {
				logger.error(procedureName+" ExecuteBatch rollback error:   " + e);
			}
			return null;
		} finally {
			try {
				conn.setAutoCommit(true);
				// if(!prestmt.isClosed())
				// {
				proc.close();
				// }
			} catch (Exception e) {
				logger.error( procedureName+ " ExecuteBatch setAutoCommit(true) error:   " + e);
			}
		}
	}


	public void Close() {
		try {
			if (rs != null) {
				rs.close();
			}

		} catch (SQLException ex) {
			logger.error("rs Close error:   " + ex);
		}
		finally
		{
			try
			{
				if (stmt != null) {
					stmt.close();
				}
			}
			catch (SQLException ex) {
				logger.error("stmt Close error:   " + ex);
			}
			finally
			{
				try
				{
					if (prestmt != null) {
						prestmt.close();
					}

				}
				catch (SQLException ex) {
					logger.error("prestmt Close error:   " + ex);
				}
				finally
				{
					try
					{
						
						if (conn != null) {
							conn.close();
						}
					}
					catch (Exception ex) {
						logger.error("pool.freeConnection error:   " + ex);
					}
				}
			}
		}
	}

	/**
	 * Factory method that creates and initializes a
	 * <code>PreparedStatement</code> object for the given SQL.
	 * <code>QueryRunner</code> methods always call this method to prepare
	 * statements for them. Subclasses can override this method to provide
	 * special PreparedStatement configuration if needed. This implementation
	 * simply calls <code>conn.prepareStatement(sql)</code>.
	 * 
	 * @param conn
	 *            The <code>Connection</code> used to create the
	 *            <code>PreparedStatement</code>
	 * @param sql
	 *            The SQL statement to prepare.
	 * @return An initialized <code>PreparedStatement</code>.
	 * @throws SQLException
	 *             if a database access error occurs
	 */
	protected PreparedStatement prepareStatement(Connection conn, String sql)
			throws SQLException {

		return conn.prepareStatement(sql);
	}

	/**
	 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
	 * 
	 * @param conn
	 *            The Connection to use to run the query. The caller is
	 *            responsible for closing this Connection.
	 * @param sql
	 *            The SQL to execute.
	 * @param params
	 *            An array of query replacement parameters. Each row in this
	 *            array is one set of batch replacement values.
	 * @return The number of rows updated per statement.
	 * @throws SQLException
	 *             if a database access error occurs
	 * @since DbUtils 1.1
	 */
	public int[] batch(String sql, Object[][] params) throws SQLException {

		

		PreparedStatement tmpStmt = null;
		int[] rows = null;
		try {
			this.conn = DBManager.generateDBConn();
			conn.setAutoCommit(false);// 取消自动提交
			tmpStmt = this.prepareStatement(conn, sql);

			for (int i = 0; i < params.length; i++) {
				this.fillStatement(tmpStmt, params[i]);
				tmpStmt.addBatch();
			}
			rows = tmpStmt.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			this.rethrow(e, sql, (Object[]) params);
		} finally {
			if (tmpStmt != null) {
				conn.rollback();
				tmpStmt.close();
			}
		}
		return rows;
	}

	/**
	 * Fill the <code>PreparedStatement</code> replacement parameters with the
	 * given objects.
	 * 
	 * @param stmt
	 *            PreparedStatement to fill
	 * @param params
	 *            Query replacement parameters; <code>null</code> is a valid
	 *            value to pass in.
	 * @throws SQLException
	 *             if a database access error occurs
	 */
	public void fillStatement(PreparedStatement stmt, Object... params)
			throws SQLException {

		if (params == null) {
			return;
		}

		ParameterMetaData pmd = null;
		if (!pmdKnownBroken) {

			pmd = stmt.getParameterMetaData();

			if (pmd.getParameterCount() < params.length) {
				throw new SQLException("Too many parameters: expected "
						+ pmd.getParameterCount() + ", was given "
						+ params.length);
			}
		}
		for (int i = 0; i < params.length; i++) {
			if (params[i] != null) {
				stmt.setObject(i + 1, params[i]);
			} else {
				// VARCHAR works with many drivers regardless
				// of the actual column type. Oddly, NULL and
				// OTHER don't work with Oracle's drivers.
				int sqlType = Types.VARCHAR;
				if (!pmdKnownBroken) {
					try {
						sqlType = pmd.getParameterType(i + 1);
					} catch (SQLException e) {
						pmdKnownBroken = true;
					}
				}
				stmt.setNull(i + 1, sqlType);
			}
		}
	}

	/**
	 * Throws a new exception with a more informative error message.
	 * 
	 * @param cause
	 *            The original exception that will be chained to the new
	 *            exception when it's rethrown.
	 * 
	 * @param sql
	 *            The query that was executing when the exception happened.
	 * 
	 * @param params
	 *            The query replacement parameters; <code>null</code> is a valid
	 *            value to pass in.
	 * 
	 * @throws SQLException
	 *             if a database access error occurs
	 */
	protected void rethrow(SQLException cause, String sql, Object... params)
			throws SQLException {

		String causeMessage = cause.getMessage();
		if (causeMessage == null) {
			causeMessage = "";
		}
		StringBuffer msg = new StringBuffer(causeMessage);

		msg.append(" Query: ");
		msg.append(sql);
		msg.append(" Parameters: ");

		if (params == null) {
			msg.append("[]");
		} else {
			msg.append(Arrays.deepToString(params));
		}

		SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
				cause.getErrorCode());
		e.setNextException(cause);

		throw e;
	}

}


